# -*- coding: utf-8 -*-
import Logger
import re
import datetime as dt
from sqlalchemy.ext.asyncio import create_async_engine, AsyncEngine
from sqlalchemy import text



# MySQL / MariaDB async connection
def mysql(user: str = 'bot', password: str = '1234567887654321',
          host: str = '192.168.0.104', port: str = '3306',
          database: str = 'treker_db') -> AsyncEngine: return \
    create_async_engine(f'mysql+aiomysql://{user}:{password}@{host}:{port}/{database}?charset=utf8mb4',
                        pool_pre_ping=True)


def between(start: dt.datetime, finish: dt.datetime = None) -> str: return \
    f'BETWEEN "{start}" AND "{(finish or start) + dt.timedelta(days=1)}"'


async def execute(field: str, db: AsyncEngine): return await sql(
    'Execute', field, '', '', '', db)


async def create(parameters: dict, table: str, db: AsyncEngine): return await sql(
    'Create', '', parameters, '', table, db)


async def select(parameters, order: str, table: str, db: AsyncEngine): return await sql(
    'Select', '', parameters, order, table, db)


async def insert(field: dict, parameters: dict, order: str, table: str, db: AsyncEngine): return await sql(
    'Insert', field, parameters, order, table, db)


async def retrieve(field: str, parameters: dict, table: str, db: AsyncEngine): return await sql(
    'Retrieve', field, parameters, '', table, db)


async def count(field: str, parameters: dict, table: str, db: AsyncEngine): return await sql(
    'Count', field, parameters, '', table, db)


async def total(field: str, parameters: dict, table: str, db: AsyncEngine): return await sql(
    'Sum', field, parameters, '', table, db)


async def delete(parameters: dict, order: str, table: str, db: AsyncEngine): return await sql(
    'Delete', '', parameters, order, table, db)


async def clear(table: str, db: AsyncEngine): return await sql(
    'Clear', '', '', '', table, db)


# MySQl requests wrapper
async def sql(action: str, field, parameters, order: str, table: str, db: AsyncEngine):
    if action == 'Execute':
        async with db.begin() as db:
            Logger.MySQL(field.replace('\n', ' '))

            variable = (await db.execute(text(field))).mappings()
            if not re.match('UPDATE|DELETE', field):
                variable = variable.all()
    elif action == 'Create':
        fields, values = '', ''
        for field, value in parameters.items():
            fields += f'`{field}`, '
            values += f'"{value}", '
        async with db.begin() as db:
            Logger.MySQL(request := f'INSERT INTO {table} ({fields[:-2]}) VALUES ({values[:-2]})')

            variable = (await db.execute(text(request))).lastrowid
    else:
        requests = {
            0: {**{key: 'SELECT' for key in ('Select', 'Retrieve', 'Count', 'Sum')},
                'Insert': 'UPDATE',
                'Delete': 'DELETE',
                'Clear': 'TRUNCATE TABLE'},
            1: {**{key: f'{action.upper()}({field})' for key in ('Count', 'Sum')},
                'Select': '*'}}
        request = \
            requests[0].get(action, '') + ' ' + \
            requests[1].get(action, '') + ' ' + \
            (f'`{field}` ' if action == 'Retrieve' else '') + \
            ('FROM ' if action in ('Select', 'Retrieve', 'Count', 'Sum', 'Delete') else '') + f'{table} ' + \
            ('SET ' + ', '.join(f'`{key}` = "{field[key]}"' for key in field) + ' ' if action == 'Insert' else '') + \
            ('WHERE ' + ' AND '.join(
                f'''`{f'{key[2:]}` {key[:1]}' if re.match('[<>]', key) else f'{key}` IN'} ("{
                '", "'.join(map(str, parameters[key])) if isinstance(parameters[key], list) else parameters[key]}")'''
                for key in parameters) + ' '
             if action in ('Select', 'Insert', 'Retrieve', 'Count', 'Sum', 'Delete') and parameters else '') + \
            (f'ORDER BY `{order[2:]}` {"ASC" if order[:1] == "+" else "DESC"} '
             if action == 'Select' and order[:1] in ('+', '-') else '') + \
            (f'LIMIT {order} ' if order == '1' or action in ('Insert', 'Delete') else '')

        async with db.begin() as db:
            Logger.MySQL(request)

            variable = (await db.execute(text(request))).mappings()
        if action in ('Select', 'Retrieve', 'Count', 'Sum'):
            variable = variable.all()
            if order in ('1', '- Time', '+ Start'):
                variable = dict(variable[0]) if variable else {}
            if action == 'Retrieve':
                variable = variable[0][field]
            if action in ('Count', 'Sum'):
                variable = variable[0][f"{action.upper()}({field})"]
    Logger.MySQL(variable)

    return variable
